package com.soft.controller;

import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

/**
 * 开发辅助数据注入接口（/api/dev）：仅开发/联调环境使用，勿在生产暴露。
 */
@RestController
@RequestMapping("/api/dev")
public class DevSeedController {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 初始化最小数据：一条用户 + 一条团队（如果不存在）。
     * 注意：仅用于开发环境，便于前后端联调。
     */
    @PostMapping("/seed-min")
    public Map<String, Object> seedMinimal() {
        // 插入用户（若存在则忽略），使用 username/email 唯一键避免重复
        jdbcTemplate.update(
                "INSERT INTO `user` (username, password, nickname, email, phone, role, status) " +
                        "VALUES ('admin', 'admin123', '管理员', 'admin@example.com', '18800000000', 'admin', 'active') " +
                        "ON DUPLICATE KEY UPDATE email=VALUES(email)"
        );

        // 确认有一个有效的 user_id
        Integer userId = jdbcTemplate.queryForObject("SELECT MIN(user_id) FROM `user`", Integer.class);

        // 插入团队（若存在则更新描述），creator_id 指向最小 user_id
        jdbcTemplate.update(
                "INSERT INTO `team` (team_name, description, creator_id) " +
                        "VALUES ('默认团队', '用于初始联调', ?) " +
                        "ON DUPLICATE KEY UPDATE description=VALUES(description)",
                userId
        );

        Integer teamId = jdbcTemplate.queryForObject("SELECT MIN(team_id) FROM `team`", Integer.class);

        Map<String, Object> resp = new HashMap<>();
        resp.put("userId", userId);
        resp.put("teamId", teamId);
        resp.put("message", "Seeded minimal data successfully");
        return resp;
    }

        // 便于在浏览器直接 GET 访问进行初始化（开发期便利）
        @GetMapping("/seed-min")
        public Map<String, Object> seedMinimalGet() {
                return seedMinimal();
        }

    /**
     * 演示数据：创建 3 个用户、2 个团队、1 个看板（含 3 列）、2 条日程、1 条看板事项。
     */
    @PostMapping("/seed-demo")
    @Transactional
    public Map<String, Object> seedDemo() {
        // 1) 用户
        jdbcTemplate.update("INSERT INTO `user` (username, password, nickname, email, phone, role, status) " +
                "VALUES ('admin', 'admin123', '管理员', 'admin@example.com', '18800000000', 'admin', 'active') " +
                "ON DUPLICATE KEY UPDATE email=VALUES(email)");
        jdbcTemplate.update("INSERT INTO `user` (username, password, nickname, email, phone, role, status) " +
                "VALUES ('alice', 'alice123', '爱丽丝', 'alice@example.com', '18800000001', 'member', 'active') " +
                "ON DUPLICATE KEY UPDATE email=VALUES(email)");
        jdbcTemplate.update("INSERT INTO `user` (username, password, nickname, email, phone, role, status) " +
                "VALUES ('bob', 'bob123', '鲍勃', 'bob@example.com', '18800000002', 'member', 'active') " +
                "ON DUPLICATE KEY UPDATE email=VALUES(email)");

        Integer adminId = firstId(jdbcTemplate.queryForList("SELECT user_id FROM `user` WHERE username='admin' LIMIT 1", Integer.class));
        Integer aliceId = firstId(jdbcTemplate.queryForList("SELECT user_id FROM `user` WHERE username='alice' LIMIT 1", Integer.class));
        Integer bobId = firstId(jdbcTemplate.queryForList("SELECT user_id FROM `user` WHERE username='bob' LIMIT 1", Integer.class));

        // 2) 团队
        jdbcTemplate.update("INSERT INTO `team` (team_name, description, creator_id) VALUES ('默认团队', '用于初始联调', ?) " +
                "ON DUPLICATE KEY UPDATE description=VALUES(description)", adminId);
        jdbcTemplate.update("INSERT INTO `team` (team_name, description, creator_id) VALUES ('产品研发团队', '产品开发协作', ?) " +
                "ON DUPLICATE KEY UPDATE description=VALUES(description)", aliceId);

        Integer defaultTeamId = firstId(jdbcTemplate.queryForList("SELECT team_id FROM `team` WHERE team_name='默认团队' LIMIT 1", Integer.class));
        Integer rdTeamId = firstId(jdbcTemplate.queryForList("SELECT team_id FROM `team` WHERE team_name='产品研发团队' LIMIT 1", Integer.class));

        // 2.1) 团队成员
        jdbcTemplate.update("INSERT INTO `team_member` (team_id, user_id, team_role, status) VALUES (?, ?, 'leader', 'active') " +
                "ON DUPLICATE KEY UPDATE team_role=VALUES(team_role)", rdTeamId, aliceId);
        jdbcTemplate.update("INSERT INTO `team_member` (team_id, user_id, team_role, status) VALUES (?, ?, 'member', 'active') " +
                "ON DUPLICATE KEY UPDATE team_role=VALUES(team_role)", rdTeamId, bobId);

        // 3) 看板（产品研发看板）
        List<Integer> existingKanban = jdbcTemplate.queryForList(
                "SELECT kanban_id FROM kanban WHERE kanban_name='产品研发看板' AND team_id=? LIMIT 1", Integer.class, rdTeamId);
        Integer kanbanId;
        if (existingKanban.isEmpty()) {
            jdbcTemplate.update("INSERT INTO kanban (kanban_name, description, team_id, creator_id) VALUES (?,?,?,?)",
                    "产品研发看板", "演示看板", rdTeamId, aliceId);
            kanbanId = firstId(jdbcTemplate.queryForList(
                    "SELECT kanban_id FROM kanban WHERE kanban_name='产品研发看板' AND team_id=? LIMIT 1", Integer.class, rdTeamId));
        } else {
            kanbanId = existingKanban.get(0);
        }

        // 3.1) 看板列：待办/进行中/已完成
        ensureColumn(jdbcTemplate, kanbanId, "待办", 1);
        ensureColumn(jdbcTemplate, kanbanId, "进行中", 2);
        ensureColumn(jdbcTemplate, kanbanId, "已完成", 3);

        Integer todoColId = firstId(jdbcTemplate.queryForList(
                "SELECT column_id FROM kanban_column WHERE kanban_id=? AND column_name='待办' LIMIT 1", Integer.class, kanbanId));

        // 4) 日程
        DateTimeFormatter fmt = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        LocalDateTime now = LocalDateTime.now();
        String s1Start = now.plusDays(1).withHour(10).withMinute(0).withSecond(0).format(fmt);
        String s1End = now.plusDays(1).withHour(11).withMinute(30).withSecond(0).format(fmt);
        jdbcTemplate.update("INSERT INTO schedule (title, content, start_time, end_time, location, status, priority, creator_id, team_id, create_time, update_time) " +
                        "VALUES (?,?,?,?,?,'todo','high',?,?,?,?)",
                "需求评审会", "讨论下一版本需求与计划", s1Start, s1End, "会议室A", aliceId, rdTeamId, s1Start, s1Start);
        Integer s1Id = firstId(jdbcTemplate.queryForList(
                "SELECT schedule_id FROM schedule WHERE title='需求评审会' AND creator_id=? ORDER BY schedule_id LIMIT 1", Integer.class, aliceId));

        String s2Start = now.plusDays(2).withHour(20).withMinute(0).withSecond(0).format(fmt);
        String s2End = now.plusDays(2).withHour(22).withMinute(0).withSecond(0).format(fmt);
        jdbcTemplate.update("INSERT INTO schedule (title, content, start_time, end_time, location, status, priority, creator_id, team_id, create_time, update_time) " +
                        "VALUES (?,?,?,?,?,'todo','medium',?,NULL,?,?)",
                "个人学习", "阅读技术文档与练习", s2Start, s2End, "家/书房", bobId, s2Start, s2Start);
        Integer s2Id = firstId(jdbcTemplate.queryForList(
                "SELECT schedule_id FROM schedule WHERE title='个人学习' AND creator_id=? ORDER BY schedule_id LIMIT 1", Integer.class, bobId));

        // 5) 看板事项：把需求评审会放入待办
        if (todoColId != null && s1Id != null) {
            List<Integer> existItem = jdbcTemplate.queryForList(
                    "SELECT item_id FROM kanban_item WHERE kanban_id=? AND schedule_id=? LIMIT 1", Integer.class, kanbanId, s1Id);
            if (existItem.isEmpty()) {
                String assignTime = now.format(fmt);
                jdbcTemplate.update("INSERT INTO kanban_item (kanban_id, schedule_id, column_id, assignee_id, assign_time, sort_order) VALUES (?,?,?,?,?,1)",
                        kanbanId, s1Id, todoColId, bobId, assignTime);
            }
        }

        Map<String, Object> resp = new HashMap<>();
        resp.put("adminId", adminId);
        resp.put("aliceId", aliceId);
        resp.put("bobId", bobId);
        resp.put("defaultTeamId", defaultTeamId);
        resp.put("rdTeamId", rdTeamId);
        resp.put("kanbanId", kanbanId);
        resp.put("scheduleIds", List.of(s1Id, s2Id));
        resp.put("message", "Seeded demo data successfully");
        return resp;
    }

    @GetMapping("/seed-demo")
    public Map<String, Object> seedDemoGet() { return seedDemo(); }

    private static Integer firstId(List<Integer> list) {
        return (list == null || list.isEmpty()) ? null : list.get(0);
    }

    private static void ensureColumn(JdbcTemplate jdbc, Integer kanbanId, String name, int order) {
        List<Integer> exists = jdbc.queryForList(
                "SELECT column_id FROM kanban_column WHERE kanban_id=? AND column_name=? LIMIT 1",
                Integer.class, kanbanId, name);
        if (exists.isEmpty()) {
            jdbc.update("INSERT INTO kanban_column (kanban_id, column_name, sort_order) VALUES (?,?,?)",
                    kanbanId, name, order);
        }
    }
}
